package com.david.mydemo.util;

import com.david.mydemo.ctl_ftp.model.ExcelEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Excel工具类
 *
 */
@Slf4j
public class ExcelUtil {

    /**excel数据最大行数设置**/
    public static final int MAX_EXCEL_ROWS = 50000;

    /***
     * excel文件输出
     * <p>每个sheet50000行</p>
     * @param fileName 文件名称
     * @param sheetName sheet名称
     * @param headList 标题list
     * @param list 数据list
     */
    public static void limitRownumDatas(HttpServletResponse response, String fileName, String sheetName, String[] headList, List<String[]> list) {
        List<ExcelEntity> mysheet = new ArrayList<ExcelEntity>();
        if (list.size() > MAX_EXCEL_ROWS) {
            int k = (list.size() + MAX_EXCEL_ROWS) / MAX_EXCEL_ROWS;
            for (int i = 1; i <= k; i++) {
                ExcelEntity excelEntity = new ExcelEntity();
                if (i < k) {
                    excelEntity.setSheetName(sheetName + i);
                    excelEntity.setHeaders(headList);
                    excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, i * MAX_EXCEL_ROWS));
                } else {
                    excelEntity.setSheetName(sheetName + i);
                    excelEntity.setHeaders(headList);
                    excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, list.size()));
                }
                mysheet.add(excelEntity);
            }
        } else {
            ExcelEntity excelEntity = new ExcelEntity(sheetName, headList, list);
            mysheet.add(excelEntity);
        }
        exportExcelSheetMulti(response, fileName, mysheet);
    }

    /***
     * excel文件输出
     * <p>多个sheet文件导出</p>
     * <p>每个sheet50000行</p>
     * @param fileName 文件名称
     * @param setSheetName sheet名称集合
     * @param mapHeadList map标题list
     * @param mapList map数据list
     */
    public static void limitRownumDatasMulti(HttpServletResponse response, String fileName, Set<String> setSheetName, Map<String, String[]> mapHeadList, Map<String, List<String[]>> mapList) {
        List<ExcelEntity> mysheet = new ArrayList<ExcelEntity>();
        for (String sheetName : setSheetName) {
            // sheetName对应的数据
            List<String[]> list = mapList.get(sheetName);
            // sheetName对应的列标题
            String[] headList = mapHeadList.get(sheetName);
            if (list.size() > MAX_EXCEL_ROWS) {
                int k = (list.size() + MAX_EXCEL_ROWS) / MAX_EXCEL_ROWS;
                for (int i = 1; i <= k; i++) {
                    ExcelEntity excelEntity = new ExcelEntity();
                    if (i < k) {
                        excelEntity.setSheetName(sheetName + i);
                        excelEntity.setHeaders(headList);
                        excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, i * MAX_EXCEL_ROWS));
                    } else {
                        excelEntity.setSheetName(sheetName + i);
                        excelEntity.setHeaders(headList);
                        excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, list.size()));
                    }
                    mysheet.add(excelEntity);
                }
            } else {
                ExcelEntity excelEntity = new ExcelEntity(sheetName, headList, list);
                mysheet.add(excelEntity);
            }
        }
        exportExcelSheetMulti(response, fileName, mysheet);
    }

    /**
     * 多sheet文件导出
     * @param fileName 文件名称
     * @param mysheets sheet设置列表
     */
    public static void exportExcelSheetMulti(HttpServletResponse response, String fileName, List<ExcelEntity> mysheets) {
        HSSFWorkbook wb = new HSSFWorkbook();// 创建工作薄
        List<ExcelEntity> sheets = mysheets;

        // 表头样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        // 字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBold(true);
        style.setFont(fontStyle);

        for (ExcelEntity excel : sheets) {
            // 新建一个sheet
            HSSFSheet sheet = wb.createSheet(excel.getSheetName());// 获取该sheet名称

            String[] handers = excel.getHeaders();// 获取sheet的标题名
            HSSFRow rowFirst = sheet.createRow(0);// 第一个sheet的第一行为标题
            // 写标题
            for (int i = 0; i < handers.length; i++) {
                // 获取第一行的每个单元格
                HSSFCell cell = rowFirst.createCell(i);
                // 往单元格里写数据
                cell.setCellValue(handers[i]);
                cell.setCellStyle(style); // 加样式
                sheet.setColumnWidth(i, 4000); // 设置每列的列宽
            }

            // 写数据集
            List<String[]> dataset = excel.getDataset();
            for (int i = 0; i < dataset.size(); i++) {
                String[] data = dataset.get(i);// 获取该对象

                // 创建数据行
                HSSFRow row = sheet.createRow(i + 1);

                for (int j = 0; j < data.length; j++) {
                    // 设置对应单元格的值
                    row.createCell(j).setCellValue(data[j]);
                }
            }
        }

        // 写文件
        try {
            log.info("write excel");
            setResponseHeader(response, fileName);
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (FileNotFoundException e) {
//            e.printStackTrace();
            log.error("导出excel报错：", e);
        } catch (IOException e) {
//            e.printStackTrace();
            log.error("导出excel报错：", e);
        }
    }

    /**
     * 发送响应流方法
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
//            ex.printStackTrace();
            log.error("导出excel，发送响应流方法报错", ex);
        }
    }

    /***
     * excel文件输出（只生成文件，不输出至浏览器）
     * <p>每个sheet50000行</p>
     * @param fileName 文件名称
     * @param sheetName sheet名称
     * @param headList 标题list
     * @param list 数据list
     */
    public static HSSFWorkbook limitRownumDatas(String fileName, String sheetName, String[] headList, List<String[]> list) {
        List<ExcelEntity> mysheet = new ArrayList<ExcelEntity>();
        if (list.size() > MAX_EXCEL_ROWS) {
            int k = (list.size() + MAX_EXCEL_ROWS) / MAX_EXCEL_ROWS;
            for (int i = 1; i <= k; i++) {
                ExcelEntity excelEntity = new ExcelEntity();
                if (i < k) {
                    excelEntity.setSheetName(sheetName + i);
                    excelEntity.setHeaders(headList);
                    excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, i * MAX_EXCEL_ROWS));
                } else {
                    excelEntity.setSheetName(sheetName + i);
                    excelEntity.setHeaders(headList);
                    excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, list.size()));
                }
                mysheet.add(excelEntity);
            }
        } else {
            ExcelEntity excelEntity = new ExcelEntity(sheetName, headList, list);
            mysheet.add(excelEntity);
        }
       return exportExcelSheetMulti(fileName, mysheet);
    }

    /**
     * 准备excel导出数据（不输出至浏览器）
     * @param fileName 文件名称
     * @param mysheets sheet设置列表
     */
    public static HSSFWorkbook exportExcelSheetMulti(String fileName, List<ExcelEntity> mysheets) {
        HSSFWorkbook wb = new HSSFWorkbook();// 创建工作薄
        List<ExcelEntity> sheets = mysheets;

        // 表头样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        // 字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBold(true);
        style.setFont(fontStyle);

        for (ExcelEntity excel : sheets) {
            // 新建一个sheet
            HSSFSheet sheet = wb.createSheet(excel.getSheetName());// 获取该sheet名称

            String[] handers = excel.getHeaders();// 获取sheet的标题名
            HSSFRow rowFirst = sheet.createRow(0);// 第一个sheet的第一行为标题
            // 写标题
            for (int i = 0; i < handers.length; i++) {
                // 获取第一行的每个单元格
                HSSFCell cell = rowFirst.createCell(i);
                // 往单元格里写数据
                cell.setCellValue(handers[i]);
                cell.setCellStyle(style); // 加样式
                sheet.setColumnWidth(i, 4000); // 设置每列的列宽
            }

            // 写数据集
            List<String[]> dataset = excel.getDataset();
            for (int i = 0; i < dataset.size(); i++) {
                String[] data = dataset.get(i);// 获取该对象

                // 创建数据行
                HSSFRow row = sheet.createRow(i + 1);

                for (int j = 0; j < data.length; j++) {
                    // 设置对应单元格的值
                    row.createCell(j).setCellValue(data[j]);
                }
            }
        }
        return wb;
    }


    /***
     * excel文件输出设置列表
     * <p>每个sheet50000行</p>
     * @param sheetName sheet名称
     * @param headList 标题list
     * @param list 数据list
     */
    public static List<ExcelEntity> limitRownumExcelEntity(String sheetName, String[] headList, List<String[]> list) {
        List<ExcelEntity> mysheet = new ArrayList<ExcelEntity>();
        if (list.size() > MAX_EXCEL_ROWS) {
            int k = (list.size() + MAX_EXCEL_ROWS) / MAX_EXCEL_ROWS;
            for (int i = 1; i <= k; i++) {
                ExcelEntity excelEntity = new ExcelEntity();
                if (i < k) {
                    excelEntity.setSheetName(sheetName + i);
                    excelEntity.setHeaders(headList);
                    excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, i * MAX_EXCEL_ROWS));
                } else {
                    excelEntity.setSheetName(sheetName + i);
                    excelEntity.setHeaders(headList);
                    excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, list.size()));
                }
                mysheet.add(excelEntity);
            }
        } else {
            ExcelEntity excelEntity = new ExcelEntity(sheetName, headList, list);
            mysheet.add(excelEntity);
        }
        return mysheet;
    }

    /***
     * excel文件输出设置列表批量
     * <p>多个sheet文件导出</p>
     * <p>每个sheet50000行</p>
     * @param setSheetName sheet名称集合
     * @param mapHeadList map标题list
     * @param mapList map数据list
     */
    public static List<ExcelEntity> limitRownumExcelEntityMulti(Set<String> setSheetName, Map<String, String[]> mapHeadList, Map<String, List<String[]>> mapList) {
        List<ExcelEntity> mysheet = new ArrayList<ExcelEntity>();
        for (String sheetName : setSheetName) {
            // sheetName对应的数据
            List<String[]> list = mapList.get(sheetName);
            // sheetName对应的列标题
            String[] headList = mapHeadList.get(sheetName);
            if (list.size() > MAX_EXCEL_ROWS) {
                int k = (list.size() + MAX_EXCEL_ROWS) / MAX_EXCEL_ROWS;
                for (int i = 1; i <= k; i++) {
                    ExcelEntity excelEntity = new ExcelEntity();
                    if (i < k) {
                        excelEntity.setSheetName(sheetName + i);
                        excelEntity.setHeaders(headList);
                        excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, i * MAX_EXCEL_ROWS));
                    } else {
                        excelEntity.setSheetName(sheetName + i);
                        excelEntity.setHeaders(headList);
                        excelEntity.setDataset(list.subList((i - 1) * MAX_EXCEL_ROWS, list.size()));
                    }
                    mysheet.add(excelEntity);
                }
            } else {
                ExcelEntity excelEntity = new ExcelEntity(sheetName, headList, list);
                mysheet.add(excelEntity);
            }
        }
        return mysheet;
    }


}
